/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cult.ticketing.dao;

import cult.ticketing.bean.ScheduleItem;
import cult.ticketing.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;


/**
 *
 * @author 51158282
 */
public class ScheduleItemDAO {
    /**
     * Get schedule items by movie id
     * @param movieId - a Integer specifying the id of a movie
     * @return a list of movie schedule items
     */
    public ArrayList<ScheduleItem> getScheduleItemByMovie(int movieId) {
        ArrayList<ScheduleItem> scheduleItems = new ArrayList<ScheduleItem>();

        //id	movie_id	house_id	price	show_time	booked_seat	na_seat
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM [schedule_Item] where [movie_id] = ?;");
            pstmt.setInt(1, movieId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                ScheduleItem item = new ScheduleItem();
                item.setId(rs.getInt(1));
                item.setMovieId(rs.getInt(2));
                item.setHouseId(rs.getInt(3));
                item.setPrice(rs.getInt(4));
                item.setShowTime(rs.getString(5));
                item.setBookedSeat(rs.getString(6));
                item.setNaSeat(rs.getString(7));

                scheduleItems.add(item);
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return scheduleItems;
    }

    public ScheduleItem getScheduleItemById(int scheduleItemId) {
        ScheduleItem scheduleItem = null;

        //[id], [movie_id], [house_id], [price], [show_time], [booked_seat], [na_seat]

        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT * FROM [schedule_Item] where [id] = ?;");
            pstmt.setInt(1, scheduleItemId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                scheduleItem = new ScheduleItem();
                scheduleItem.setId(rs.getInt(1));
                scheduleItem.setMovieId(rs.getInt(2));
                scheduleItem.setHouseId(rs.getInt(3));
                scheduleItem.setPrice(rs.getInt(4));
                scheduleItem.setShowTime(rs.getString(5));
                scheduleItem.setBookedSeat(rs.getString(6));
                scheduleItem.setNaSeat(rs.getString(7));
            }

            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return scheduleItem;
    }

    public boolean checkSeatAvailability(String bookedSeat, String seatIndex) {
        boolean ifAvailable = true;
        String[] booked = bookedSeat.split(";");
        String[] toBeBooked = seatIndex.split(";");
        
        for (int i=0; i<booked.length; i++) {
            for (int j=0; j<toBeBooked.length; j++)
                if (toBeBooked[j].equals(booked[i]))
                    ifAvailable = false;
        }
        return ifAvailable;
    }

    public boolean updateBookedSeat(int scheduleItemId, String seatIndex) {
        boolean isSuccessful = false;
        String bookedSeat = "";
        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT [booked_seat] FROM [schedule_Item] where [id] = ?;");
            pstmt.setInt(1, scheduleItemId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                bookedSeat = rs.getString(1);
            }

            if (checkSeatAvailability(bookedSeat, seatIndex)) {
                bookedSeat = bookedSeat + seatIndex;
                pstmt = con.prepareStatement("UPDATE [schedule_item] SET [booked_seat] = ? WHERE [id] = ?;");
                pstmt.setString(1, bookedSeat);
                pstmt.setInt(2, scheduleItemId);
                if (pstmt.executeUpdate() > 0) {
                    isSuccessful = true;
                }
            }
            
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return isSuccessful;
    }

    public boolean addNewScheduleItem(int movieID,int houseID,int price,String showTime,String bookedSeat,String naSeat){
    boolean ifAddSuccessful = true;

                try {
                    DataSource ds = DB.getDataSource();
                    Connection con = ds.getConnection();
                    PreparedStatement pstmt = con.prepareStatement("INSERT INTO schedule_item([movie_id], [house_id], [price], [show_time], [booked_seat], [na_seat])VALUES(?, ?, ?, ?, ?, ?);");
                        pstmt.setInt(1, movieID);
                        pstmt.setInt(2, houseID);
                        pstmt.setInt(3, price);
                        pstmt.setString(4, showTime);
                        pstmt.setString(5, bookedSeat);
                        pstmt.setString(6, naSeat);


                         int r = pstmt.executeUpdate();
                         if (r <= 0) {
                            ifAddSuccessful = false;
                        }


                        if (pstmt != null) {
                            pstmt.close();
                        }
                        if (con != null) {
                            con.close();
                        }
                    } catch (NamingException ne) {

                    } catch (SQLException sqle) {

                    }


		return ifAddSuccessful;

    }

    public boolean releaseBookedSeat(int scheduleItemId, String seatIndex) {
        boolean isSuccessful = false;
        String bookedSeat = "";

        try {
            DataSource ds = DB.getDataSource();
            Connection con = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement("SELECT [booked_seat] FROM [schedule_Item] where [id] = ?;");
            pstmt.setInt(1, scheduleItemId);
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                bookedSeat = rs.getString(1);
            }

            int index = bookedSeat.indexOf(seatIndex);
            bookedSeat = bookedSeat.substring(0, index) + bookedSeat.substring(index + seatIndex.length() + 1);

            pstmt = con.prepareStatement("UPDATE [schedule_item] SET [booked_seat] = ? WHERE [id] = ?;");
            pstmt.setString(1, bookedSeat);
            pstmt.setInt(2, scheduleItemId);
            if (pstmt.executeUpdate() > 0) {
                isSuccessful = true;
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return isSuccessful;
    }
}
